
import static java.lang.Boolean.FALSE;
import static java.lang.Boolean.TRUE;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;


/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 *
 * @author JSJSYS
 */
public class userdao {
     //驱动类，H2jar包中可以找到
     static final String DRIVER_CLASS = "org.h2.Driver";
    //数据库连接URL，当前连接的是E:/H2目录下的mydb数据库
   // static final String JDBC_URL = "jdbc:h2:E:/H23/mydb";
      static final String JDBC_URL = "jdbc:h2:tcp://localhost/C:/Users/JSJSYS/Desktop/test";
     // static final String JDBC_URL = "jdbc:h2:tcp://localhost/mem:mydb";
    //用户名
     static final String USER = "sa";
    //密码
     static final String PASSWORD = "123456";
     private Connection connection;
    // 定义sql语句的执行对象  
    private PreparedStatement pstmt;
    // 定义查询返回的结果集合  
    private ResultSet resultSet;
    public DbUtils() {

    }

    /**
     *
     *
     * 获取数据库连接
     *
     *
     *
     *
     *
     * @return 数据库连接
     *
     *
     */
    public Connection getConnection() {

        try {

            Class.forName(DRIVER); // 注册驱动  

            connection = DriverManager.getConnection(URL, USERNAME, PASSWORD); // 获取连接  

        } catch (Exception e) {

            throw new RuntimeException("get connection error!", e);

        }

        return connection;

    }

    /**
     *
     *
     * 执行更新操作
     *
     *
     *
     *
     *
     * @param sql sql语句
     *
     *
     * @param params 执行参数
     *
     *
     * @return 执行结果
     *
     *
     * @throws SQLException
     *
     *
     */
    public boolean updateByPreparedStatement(String sql, List<?> params)
            throws SQLException {

        boolean flag = false;

        int result = -1;// 表示当用户执行添加删除和修改的时候所影响数据库的行数  

        pstmt = connection.prepareStatement(sql);

        int index = 1;

        // 填充sql语句中的占位符  
        if (params != null && !params.isEmpty()) {

            for (int i = 0; i < params.size(); i++) {

                pstmt.setObject(index++, params.get(i));

            }

        }

        result = pstmt.executeUpdate();

        flag = result > 0 ? true : false;

        return flag;

    }

    /**
     *
     *
     * 执行查询操作
     *
     *
     *
     *
     *
     * @param sql sql语句
     *
     *
     * @param params 执行参数
     *
     *
     * @return
     *
     *
     * @throws SQLException
     *
     *
     */
    public List<Map<String, Object>> findResult(String sql, List<?> params)
            throws SQLException {

        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();

        int index = 1;

        pstmt = connection.prepareStatement(sql);

        if (params != null && !params.isEmpty()) {

            for (int i = 0; i < params.size(); i++) {

                pstmt.setObject(index++, params.get(i));

            }

        }

        resultSet = pstmt.executeQuery();

        ResultSetMetaData metaData = resultSet.getMetaData();

        int cols_len = metaData.getColumnCount();

        while (resultSet.next()) {

            Map<String, Object> map = new HashMap<String, Object>();

            for (int i = 0; i < cols_len; i++) {

                String cols_name = metaData.getColumnName(i + 1);

                Object cols_value = resultSet.getObject(cols_name);

                if (cols_value == null) {

                    cols_value = "";

                }

                map.put(cols_name, cols_value);

            }

            list.add(map);

        }

        return list;

    }

    /**
     *
     *
     * 释放资源
     *
     *
     */
    public void releaseConn() {

        if (resultSet != null) {

            try {

                resultSet.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

        if (pstmt != null) {

            try {

                pstmt.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

        if (connection != null) {

            try {

                connection.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

    }



}


     
//     private static int num =1;
//     private Statement stmt;
//     private Connection conn;
//     private String dboper="";
//     private ResultSet rs;
//    private static int getId(){
//        return num++  ;
//    }
//    static void p(String a){
//        System.out.println(a);
//    }
//    private static void show( Statement stmt) throws Exception{
//        //查询
//        ResultSet rs = stmt.executeQuery("SELECT * FROM stu_info");
//          while (rs.next()) {
//            p(rs.getString(1) + "\t" + rs.getString("name") + "\t" + rs.getString("sex")+"\t"+rs.getString(4));
//        }
//    }
//    public boolean initDB() throws ClassNotFoundException {
//
//        Class.forName(DRIVER_CLASS);
//        // 根据连接URL，用户名，密码获取数据库连接
//        try {
//            conn = DriverManager.getConnection(JDBC_URL, USER, PASSWORD);
//            stmt = conn.createStatement();
//        } catch (SQLException ex) {
//            Logger.getLogger(userdao.class.getName()).log(Level.SEVERE, null, ex);
//            return FALSE;
//        }
//        return TRUE;
//     }
//    public void createBiao() throws SQLException {
//
//        stmt.execute("DROP TABLE IF EXISTS login ");
//
//        stmt.execute("CREATE TABLE login(id VARCHAR(20) PRIMARY KEY,psw varchar(20))");
//
//        dboper += "添加表Login表，数据名字为用户名和密码" + "\n";
//
//    }
//
//    public void createvalues(userinfo tmp) throws SQLException {
//        String stmp = "";
//        stmp += "INSERT INTO Login VALUES('" + tmp.id + "','" + tmp.psw + "')";
//        dboper += "账号：" + tmp.id + " 密码：" + tmp.psw + "\n";
//        stmt.executeUpdate(stmp);
//    }
//    public ResultSet selectalluser() throws SQLException {
//        String stmp = "select * from login";
//        rs = stmt.executeQuery(stmp);
//        return rs;
//    }
//
//    public boolean closeDB() throws SQLException {
//        stmt.close();
//        conn.close();
//        return TRUE;
//    }
//    public String getdboper() {
//        return dboper;
//    }
}
